﻿#include "databasesql.h"
#include <QSqlError>
#include <QSqlQuery>
#include <QVariant>
#include "common_log.h"


DataBaseSql::DataBaseSql(const QString& path)
{
	m_db = QSqlDatabase::addDatabase("QSQLITE", "VISDB");
	m_db.setDatabaseName(path);
	if (!m_db.open()) {
		log_error("{} 数据库打开失败,{}", path, m_db.lastError().text());
	}
}

DataBaseSql::~DataBaseSql()
{
	if (m_db.isOpen()) {
		m_db.close();
	}
}

// 创建表，表的格式固定（INT TXT: id, data）
bool DataBaseSql::CreateDBTable(const QString& tablename)
{
	if (!m_db.isOpen()) {
		log_error("数据库打开失败");
		return false;
	}
	QStringList key = { "id", "data" };
	QStringList type = { "INT PRIMARY KEY", "TEXT" };

	if (IfTableExist(tablename)) {
		QSqlRecord rr = m_db.record(tablename);
		QStringList nkey;
		QStringList ntype;
		QString sql;
		int index = 0;
		for (; index < key.size(); index++) {
			if (!rr.contains(key[index])) {
				if (index < type.size()) {
					sql = QString("ALTER TABLE ") + tablename + QString(" add column ");
					sql += key[index];
					sql += " ";
					sql += type[index];
					nkey << key[index];
					ntype << type[index];
					QSqlQuery qurry(m_db);
					if (!qurry.exec(sql)) {
						return false;
					}
				}
			}
		}
		return true;
	}
	QString sql = QString("CREATE TABLE IF NOT EXISTS ") + tablename + QString("(");
	for (int i = 0; i < key.size(); i++) {
		sql += key[i];
		sql += QString(" ");
		sql += type[i];
		if (i == key.size() - 1) {
			sql += QString(") ");
		} else {
			sql += QString(",");
		}
	}
	QSqlQuery qurry(m_db);
	if (!qurry.exec(sql)) {
		log_error("exec error: {}", qurry.lastError().text());
		return false;
	}
	return true;

}

// 增加数据
bool DataBaseSql::AddData(const QString& tablename, int id, const QByteArray& data)
{
	log_trace("insert data  to table [{}], id = {}", tablename, id);
	QWriteLocker lock(&rw_lock);
	if (!IfTableExist(tablename)) {
		log_error("{} not exit", tablename);
		return false;
	}
	if (IfTableHasId(tablename, id)) {
		log_info("{}中的{}重复，更新数据", tablename, id);
		lock.unlock();
		return  UpdateData(tablename, id, data);
	}

	QString sql = "INSERT INTO " + tablename + " ( id,data ) ";
	sql += QString("VALUES(") + QString::number(id) + ",\'";
	sql += data;
	sql += QString("\')");	

	QSqlQuery qurry(m_db);
	if (!qurry.exec(sql)) {
		log_error("exec error: {}", qurry.lastError().text());
		return false;
	}
	return true;
}

// 删除数据（通过id判断, id = 0 删除全部数据）
bool DataBaseSql::DeleteData(const QString& tablename, int id)
{
	log_trace("Delet data [{}] form [{}]", id, tablename);
	QWriteLocker lock(&rw_lock);
	if (!IfTableExist(tablename)) {
		log_error("{} not exit", tablename);
		return false;
	}
	if (id != 0) {
		QString sql = "DELETE FROM " + tablename + " WHERE id = " + QString::number(id);
		QSqlQuery qurry(m_db);
		if (!qurry.exec(sql)) {
			log_error("exec error: {}", qurry.lastError().text());
			return false;
		}
		return true;
	} else {
		QString sql = "DELETE FROM " + tablename;
		QSqlQuery qurry(m_db);
		if (!qurry.exec(sql)) {
			log_error("exec error: {}", qurry.lastError().text());
			return false;
		}
		return true;
	}

}

// 更新数据
bool DataBaseSql::UpdateData(const QString& tablename, int id, const QByteArray& data)
{
	QWriteLocker lock(&rw_lock);

	if (!IfTableExist(tablename)) {
		log_error("{} not exit", tablename);
		return false;
	}

	if (!IfTableHasId(tablename, id)) {
		log_error("{} 没有找到id = {}的数据，无法更新", tablename, id);
		return false;
	}


	QString sql = "UPDATE " + tablename + " SET data = '";
	sql += data;
	sql += ("' WHERE id = " + QString::number(id));
	QSqlQuery qurry(m_db);
	if (!qurry.exec(sql)) {
		log_error("exec error: {}", qurry.lastError().text());
		return false;
	}
	return true;
}

// 读取数据 id =0, 读取全部数据
QByteArrayList DataBaseSql::ReadDataById(const QString& tablename, int id)
{
	QReadLocker lock(&rw_lock);
	if (!IfTableExist(tablename)) {
		return {};
	}

	QString sql = "SELECT * FROM " + tablename;
	if (id > 0) {
		sql += " WHERE id = ";
		sql += QString::number(id);
	}

	QSqlQuery qurry(m_db);
	if (!qurry.exec(sql)) {
		log_error("exec error: {}", qurry.lastError().text());
		return {};
	}
	QByteArrayList ret;
	while (qurry.next()) {
		if (id > 0) {
			if (qurry.value("id").toInt() == id) {
				ret.push_back(qurry.value("data").toByteArray());
			}
		} else {
			ret.push_back(qurry.value("data").toByteArray());
		}
	}

	return ret;
}

/*判断表内是否存在该条数据*/
bool DataBaseSql::IfTableHasId(const QString& tablename, int id)
{
	QString sql = "SELECT * FROM " + tablename + " activity ";
	sql += (" WHERE id = " + QString::number(id) + " LIMIT 1");
	QSqlQuery qurry(m_db);
	if (!qurry.exec(sql)) {
		log_error("exec error: {}", qurry.lastError().text());
		return false;
	}
	if (qurry.next()) {
		return qurry.value(0).toInt() > 0;
	}
	return false;
}

/*判断表是否存在*/
bool DataBaseSql::IfTableExist(const QString& tablename)
{
	return m_db.tables().contains(tablename, Qt::CaseInsensitive);
}
